Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tuning Methodology

This section explains why it is important to follow a structured, goal-oriented methodology in your tuning efforts if you want to achieve optimal results. The methodology I use involves these five steps as shown in Figure 4.1.


Figure 4.1  The tuning methodology flowchart.

1.  Analyze the system. Decide if there is a problem; if there is a problem, what is it?
2.  Determine the problem. What do you think is causing the problem? Why?
3.  Determine a solution and set goals. Decide what you want to try and what you think will result from your changes. What is it you want to accomplish? Do you want more throughput? faster response times? what?
4.  Test the solution. Try it. See what happens.
5.  Analyze the results. Did the solution meet the goals? If not, you may have to go back to step 1, 2, or 3.

The following sections describe each of these steps individually and explain why each step is important to the final outcome. Sometimes, there are situations for which you don’t have an answer to the problem or perhaps you don’t even know what the problem is. In the long run, however, a little analysis is worth more than a lot of trial and error.

Examine the Problem

The first phase in tuning the system should always be the determination phase. First, see whether you even have a problem. You may just be at the limits of the configuration. To make sure, examine the system, look for problems, and make this determination logically.

The determination phase consists of examining the system as it currently is, looking for problems or bottlenecks, and identifying “hot spots.” Keep in mind that performance problems may not always be obvious. Here is a partial list of some areas you should examine:

  Application code. The problem may consist of an application that executes excessive code or performs table scans when they are not necessary.
  Oracle database engine. The problem may be an improperly sized System Global Area (SGA) or some other tuning parameters.
  Operating System parameters. The OS may not be configured properly and may be starving Oracle of needed resources.
  Hardware configuration. The layout of the database may not be efficient and may be causing a disk bottleneck.
  Network. The network may be overloaded and causing excessive collisions and delays.

I prefer to categorize performance issues into the following three classes:

  It’s broken. Performance is severely handicapped because of a configuration error or an incorrect parameter in the OS or RDBMS. I consider problems that fall into this category to cause a performance swing of 50 percent or more. Problems in this category are usually oversights during the system build (such as incorrectly building an index or forgetting to enable Asynchronous I/O).
  It’s not optimized. Performance is slightly degraded because of a small miscalculation in parameters or because system capacity is slightly exceeded. These types of problems are usually easily solved by fine tuning the configurations.
  Not a problem. Don’t forget that sometimes there isn’t a problem—you have just reached the capacity of the system. This situation can easily be solved by upgrading or adding more capacity. Not all problems can be solved with tuning.

For the first class of problem, you may have to take drastic action. Tuning may solve the second class of problem. For the third class of problem, you need take no action.

Here are some questions to ask yourself when examining the system and searching for problems:

  Are you getting complaints from the user community?
  Are some operations taking much longer than the same operations did in the past?
  Is CPU usage low but I/O usage high?
  Are you seeing excessive response times?
  Does the system seem sluggish?

If you answer yes to any of these questions, you may be seeing some performance problems that can be fixed. In some cases, you have reached the limitations of your hardware and should add an additional CPU or perhaps some more disks.

If you answer no to all these questions, your system may be running very well. However, you should still periodically monitor the performance of the system to avoid future problems.

Determine the Problem

Once you have decided that there is some sort of problem in the system, make a careful analysis to determine the cause of the problem.

At first glance, it is not always obvious what the problem is. You may see excessive disk I/O on a certain table that may lead you to believe you have an I/O problem when, in fact, you have a cache-hit problem or an index problem that is causing excessive I/Os.

Take a few minutes to think about the problem. Decide what area you believe the problem to be in. Thinking through the problem can help you avoid a long trial-and-error period in which you randomly try different things without positive results.

At this stage, note-taking is a must. By writing down your ideas and thoughts, you can better organize them. If you have to go back and rethink the problem, your notes will be very useful.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.